#region Using directives
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using webapp4U.BOL;
#endregion


namespace webapp4U.DAL
{
	 public class ShoppingCartDAL : BaseDAL
	 {
	     #region ObjIDataReader

	     /// <summary>
	     /// Returns a new ShoppingCart instance filled with the DataReader's current record data 
	     /// </summary>
	     private ShoppingCart GetShoppingCartFromReader(IDataReader dr)
	     {
		     ShoppingCart obj = new ShoppingCart();
		     obj.ShoppingCartID = (dr["ShoppingCartID"] is DBNull) ? Int32.MinValue :  (Int32)dr["ShoppingCartID"];
		     obj.AddedDate = (dr["AddedDate"] is DBNull) ? DateTime.MinValue :  (DateTime)dr["AddedDate"];
		     obj.UserID = (dr["UserID"] is DBNull) ? String.Empty :  Convert.ToString(dr["UserID"]);
		     obj.ProductID = (dr["ProductID"] is DBNull) ? Int32.MinValue :  (Int32)dr["ProductID"];
		     obj.Title = (dr["Title"] is DBNull) ? String.Empty :  (String)dr["Title"];
		     obj.SKU = (dr["SKU"] is DBNull) ? String.Empty :  (String)dr["SKU"];
		     obj.UnitPrice = (dr["UnitPrice"] is DBNull) ? Decimal.MinValue :  (Decimal)dr["UnitPrice"];
		     obj.Quantity = (dr["Quantity"] is DBNull) ? Int32.MinValue :  (Int32)dr["Quantity"];
		     obj.Subtotal = (dr["Subtotal"] is DBNull) ? Decimal.MinValue :  (Decimal)dr["Subtotal"];
		     return obj;
	     }
	     #endregion

	     /// <summary>
	     /// Returns a collection of ShoppingCart with the data read from the input DataReader
	     /// </summary>
	     private List<ShoppingCart> GetShoppingCartCollectionFromReader(IDataReader dr)
	     {
		     List<ShoppingCart> obj = new List<ShoppingCart>();
		     while(dr.Read())
			     obj.Add(GetShoppingCartFromReader(dr));
		     return obj;
	     }

         //select sum(UnitPrice * Quantity) as SubTotal from w4u_ShoppingCart
         public Decimal GetSubTotalShoppingCart(string UserID)
         {
             using (SqlConnection myConn = GetConnectionString())
             {
                 SqlCommand myCommand = new SqlCommand("w4u_ShoppingCart_GetSubTotal", myConn);
                 myCommand.Parameters.Add("@UserID", SqlDbType.VarChar).Value = UserID;
                 myCommand.CommandType = CommandType.StoredProcedure;
                 return Convert.ToDecimal(myCommand.ExecuteScalar());
             }
         }

         public Boolean CheckShoppingCart(ShoppingCart obj)
         {
             using (SqlConnection myConn = GetConnectionString())
             {
                 try
                 {
                     SqlCommand myCommand = new SqlCommand("w4u_ShoppingCart_Check", myConn);
                     myCommand.CommandType = CommandType.StoredProcedure;
                     myCommand.Parameters.Add("@UserID", SqlDbType.VarChar).Value = obj.UserID;
                     myCommand.Parameters.Add("@ProductID", SqlDbType.Int).Value = obj.ProductID;
                     string value = Convert.ToString(myCommand.ExecuteScalar());
                     if (value.Trim().Length > 1)
                         return true;
                     return false;
                 }
                 catch
                 {
                     return false;
                 }
             }
         }

	     public int Insert(ShoppingCart obj)
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
		     SqlCommand myCommand = new SqlCommand("w4u_ShoppingCart_Insert", myConn); 
		     myCommand.CommandType = CommandType.StoredProcedure; 
		     myCommand.Parameters.Add("@ShoppingCartID", SqlDbType.Int).Direction = ParameterDirection.Output;
		     myCommand.Parameters.Add("@AddedDate", SqlDbType.DateTime).Value = obj.AddedDate;
		     myCommand.Parameters.Add("@UserID", SqlDbType.VarChar).Value = obj.UserID;
		     myCommand.Parameters.Add("@ProductID", SqlDbType.Int).Value = obj.ProductID;
		     myCommand.Parameters.Add("@Title", SqlDbType.NVarChar).Value = obj.Title;
		     myCommand.Parameters.Add("@SKU", SqlDbType.NVarChar).Value = obj.SKU;
		     myCommand.Parameters.Add("@UnitPrice", SqlDbType.Money).Value = obj.UnitPrice;
		     myCommand.Parameters.Add("@Quantity", SqlDbType.Int).Value = obj.Quantity;
		     myCommand.Parameters.Add("@Subtotal", SqlDbType.Money).Value = obj.Subtotal;

		     int ret = myCommand.ExecuteNonQuery();
		     return (int)myCommand.Parameters["@ShoppingCartID"].Value; 
		     }
	     }

         // cap nhat so luong
         public bool UpdateQuantity(ShoppingCart obj)
         {
             using (SqlConnection myConn = GetConnectionString())
             {
                 SqlCommand myCommand = new SqlCommand("w4u_ShoppingCart_UpdateQuantity", myConn);
                 myCommand.CommandType = CommandType.StoredProcedure;
                 myCommand.Parameters.Add("@UserID", SqlDbType.VarChar).Value = obj.UserID;
                 myCommand.Parameters.Add("@ProductID", SqlDbType.Int).Value = obj.ProductID;
                 int ret = myCommand.ExecuteNonQuery();
                 return (ret == 1);
             }
         }


	     public bool Update(ShoppingCart obj)
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
		         SqlCommand myCommand = new SqlCommand("w4u_ShoppingCart_Update", myConn); 
		         myCommand.CommandType = CommandType.StoredProcedure;
                 myCommand.Parameters.Add("@UserID", SqlDbType.VarChar).Value = obj.UserID;
		         myCommand.Parameters.Add("@ProductID", SqlDbType.Int).Value = obj.ProductID;
		         myCommand.Parameters.Add("@Quantity", SqlDbType.Int).Value = obj.Quantity;
		         myCommand.Parameters.Add("@Subtotal", SqlDbType.Money).Value = obj.Subtotal;

		         int ret = myCommand.ExecuteNonQuery();
		         return (ret == 1);
		     }
	     }

         public bool Delete(Int32 ProductID)
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
		     SqlCommand myCommand = new SqlCommand("w4u_ShoppingCart_Delete", myConn); 
		     myCommand.CommandType = CommandType.StoredProcedure;
             myCommand.Parameters.Add("@ProductID", SqlDbType.Int).Value = ProductID;

		     int ret = myCommand.ExecuteNonQuery();
		     return (ret == 1);
		     }
	     }

	     /// <summary>
	     /// Retrieves the w4u_ShoppingCart with the specified ID
	     /// </summary>
	     public ShoppingCart GetShoppingCartByID(Int32 ShoppingCartID)
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
		     SqlCommand myCommand = new SqlCommand("w4u_ShoppingCart_GetShoppingCartByID", myConn); 
		     myCommand.CommandType = CommandType.StoredProcedure; 
		     myCommand.Parameters.Add("@ShoppingCartID", SqlDbType.Int).Value = ShoppingCartID;

		     SqlDataReader dr = myCommand.ExecuteReader();
		     return (dr.Read() ? GetShoppingCartFromReader(dr) : null);
		     }
	     }

	     //Get All ShoppingCart
	     public List<ShoppingCart> GetAllShoppingCart(int PageIndex, int PageSize)
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
		     SqlCommand myCommand = new SqlCommand("w4u_ShoppingCart_GetAllShoppingCart", myConn); 
		     myCommand.CommandType = CommandType.StoredProcedure; 
		     myCommand.Parameters.Add("@PageIndex", SqlDbType.Int).Value = PageIndex;
		     myCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value = PageSize;
		     SqlDataReader dr = myCommand.ExecuteReader();
		     return GetShoppingCartCollectionFromReader(dr);
		     }
	     }

	     // Get Count All records of Table  ShoppingCart
	     public int GetCountShoppingCart()
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
		     SqlCommand myCommand = new SqlCommand("w4u_ShoppingCart_GetCountShoppingCart", myConn); 
		     myCommand.CommandType = CommandType.StoredProcedure; 
		     return (int)myCommand.ExecuteScalar();
		     }
	     }

	     // Get ShoppingCartByUserID
	     public List<ShoppingCart> GetShoppingCartByUserID(String UserID,int PageIndex, int PageSize)
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
		     SqlCommand myCommand = new SqlCommand("w4u_ShoppingCart_GetShoppingCartByUserID", myConn); 
		     myCommand.CommandType = CommandType.StoredProcedure; 
		     myCommand.Parameters.Add("@UserID", SqlDbType.VarChar).Value = UserID;
		     myCommand.Parameters.Add("@PageIndex", SqlDbType.Int).Value = PageIndex;
		     myCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value = PageSize;
		     SqlDataReader dr = myCommand.ExecuteReader();
		     return GetShoppingCartCollectionFromReader(dr);
		     }
	     }

	     // Get ShoppingCartCountByUserID
	     public int GetShoppingCartCountByUserID(String UserID)
	     {
		     using (SqlConnection myConn = GetConnectionString())
		     {
		         SqlCommand myCommand = new SqlCommand("w4u_ShoppingCart_GetShoppingCartCountByUserID", myConn); 
		         myCommand.CommandType = CommandType.StoredProcedure; 
		         myCommand.Parameters.Add("@UserID", SqlDbType.VarChar).Value = UserID;
		         return (int)myCommand.ExecuteScalar();
		     }
	     }

	 }
}
